package com.zhan.ielts.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Cet4And6WordUtils {

	static String className = "com.mysql.jdbc.Driver";
	static String url = "jdbc:mysql://192.168.1.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
	static String username = "ieltsdba";
	static String userpass = "zaq1_XSW2";
	static Connection con = null;
	static PreparedStatement pstmt = null;
	static PreparedStatement stmt = null;
	static ResultSet rs = null;

	public static void main(String[] args) throws FileNotFoundException, IOException {
		getConnection();
		// String filepath1 = "D:/xiaozhan/yasi/hyfer/托福文章导出/雅思文章.xlsx";
		// write2007Excel(filepath1, "ielts");
	}

	private static void getConnection() throws FileNotFoundException, IOException {
		// String className = "com.mysql.jdbc.Driver";
		// String url =
		// "jdbc:mysql://192.168.1.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
		// String username = "ieltsdba";
		// String userpass = "zaq1_XSW2";
		// Connection con = null;
		// PreparedStatement pstmt = null;
		// PreparedStatement stmt = null;
		// ResultSet rs = null;

		//		String filepath = "D:/xiaozhan/received files/jiang qian/0324/2013年大学英语六级词汇表.xlsx";
		String filepath = "D:/xiaozhan/received files/jiang qian/0324/俞敏洪：四级词汇词根+联想记忆法（Excel版本）.xlsx";
//		String filepath = "D:/xiaozhan/received files/jiang qian/0324/新东方红宝书词库.xlsx";

		File myFile = new File(filepath);
		// File myFile = new File("d://tmp/Employee.xlsx");
		XSSFWorkbook wb;

		wb = new XSSFWorkbook(new FileInputStream(filepath));
		// 创建一个张表
		XSSFSheet sheet = wb.getSheetAt(0);

		try {
			Class.forName(className);
			con = DriverManager.getConnection(url, username, userpass);
			con.setAutoCommit(false);
			// pstmt =
			// con.prepareStatement("select word , exam_type from t_word_ielts where word =  ? ");
			stmt = con.prepareStatement("replace into t_word_cet4(word,exam_type) VALUES (?, ? );");
//			stmt.setString(1, "a");
//			stmt.setString(2, "2");
//			
			
			XSSFRow row = null;
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				row = sheet.getRow(i);
				String word = String.valueOf(row.getCell((short) 2));
				System.out.println(word);
				stmt.setString(1, word.trim());
				stmt.setString(2, "");
				
				stmt.addBatch();
				if(i%100==0){
                    stmt.executeBatch();
                    con.commit();
                    con.setAutoCommit(false);
                }
			}
			
			
//			stmt.execute();
			stmt.executeBatch();
			con.commit();
			con.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void write2007Excel(String filePath, String type) {
		try {
			File myFile = new File(filePath);
			// File myFile = new File("d://tmp/Employee.xlsx");
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));

			// 创建excel文件对象
			// XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myFile));

			// 创建一个张表
			XSSFSheet sheet = wb.getSheetAt(1);
			XSSFSheet sheet3 = null;
			if (wb.getSheet("听力-去除时间") != null) {
				sheet3 = wb.getSheet("听力-去除时间");
			} else {
				sheet3 = wb.createSheet("听力-去除时间");
			}

			sheet3.setColumnWidth(1, 13 * 256);
			sheet3.setColumnWidth(2, 17 * 256);
			sheet3.setColumnWidth(3, 17 * 256);
			sheet3.setColumnWidth(4, 17 * 256);
			sheet3.setColumnWidth(5, 17 * 256);
			sheet3.setColumnWidth(6, 17 * 256);
			sheet3.setColumnWidth(7, 17 * 256);

			sheet3.setColumnWidth(8, 17 * 256);
			sheet3.setColumnWidth(9, 17 * 256);
			sheet3.setColumnWidth(10, 17 * 256);
			sheet3.setColumnWidth(11, 17 * 256);
			sheet3.setColumnWidth(12, 17 * 256);
			sheet3.setColumnWidth(13, 17 * 256);

			// 创建行对象
			XSSFRow row = null;
			XSSFRow rowNew = null;
			// 创建表格对象
			XSSFCell cell = null;

			// 循环行
			// for (int i = sheet.getFirstRowNum(); i <=
			// sheet.getPhysicalNumberOfRows(); i++) {
			for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
				row = sheet.getRow(i);

				if (row == null) {// 判断是否为空
					row = sheet.createRow(i);
					// continue;
				}

				// 循环列
				for (int j = 0; j < row.getLastCellNum(); j++) {
					if (sheet3.getRow(i) != null) {
						rowNew = sheet3.getRow(i);
					} else {
						rowNew = sheet3.createRow(i);
					}

					cell = rowNew.createCell(j);// 创建单元格
					String m = String.valueOf(row.getCell((short) j));
					// cell.setCellValue(m);// 赋值
					// if("tpo".equalsIgnoreCase(type)){
					// cell.setCellValue(removeTimeFlagTpo(m));// 赋值
					// }else{
					// cell.setCellValue(removeTimeFlagIelts(m));// 赋值
					// }

					CellStyle style = wb.createCellStyle();
					style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
					style.setFillPattern(CellStyle.SOLID_FOREGROUND);
					style.setWrapText(true);
					cell.setCellStyle(style);
				}

			}

			// FileOutputStream out = new FileOutputStream(filePath);
			// wb.write(out);
			// out.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
